package cn.stylefeng.guns.sys.modular.system.service.impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.stylefeng.guns.base.auth.context.LoginContextHolder;
import cn.stylefeng.guns.base.pojo.page.LayuiPageFactory;
import cn.stylefeng.guns.base.pojo.page.LayuiPageInfo;
import cn.stylefeng.guns.sys.core.util.*;
import cn.stylefeng.guns.sys.core.util.DateUtil;
import cn.stylefeng.guns.sys.modular.system.controller.BaseFrontController;
import cn.stylefeng.guns.sys.modular.system.entity.*;
import cn.stylefeng.guns.sys.modular.system.mapper.*;
import cn.stylefeng.guns.sys.modular.system.model.params.*;
import cn.stylefeng.guns.sys.modular.system.model.result.*;
import cn.stylefeng.guns.sys.modular.system.service.*;
import cn.stylefeng.roses.core.reqres.response.ErrorResponseData;
import cn.stylefeng.roses.core.reqres.response.ResponseData;
import cn.stylefeng.roses.core.reqres.response.SuccessResponseData;
import cn.stylefeng.roses.core.util.ToolUtil;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import static org.apache.poi.ss.usermodel.CellType.NUMERIC;

/**
 * <p>
 * bty_zx_demo 服务实现类
 * </p>
 *
 * @author liuy
 * @since 2020-11-02
 */
@Service
public class ZxBxServiceImpl extends ServiceImpl<ZxBxMapper, ZxBx> implements ZxBxService {
    Logger logger = LoggerFactory.getLogger(ZxBxServiceImpl.class);

    @Resource
    private MdzxMapper mdzxMapper;
    @Resource
    private MdzxService mdzxService;
    @Resource
    private ProjectMapper projectMapper;
    @Resource
    private ZxHuopinMapper zxHuopinMapper;
    @Resource
    private BtyBxdaMapper btyBxdaMapper;
    @Resource
    private ZxKemuMapper zxKemuMapper;
    @Resource
    private ZxKemuKeyMapper zxKemuKeyMapper;
    @Resource
    private ZxBxMingxiMapper zxBxMingxiMapper;
    @Resource
    private ZxBxMingxiService zxBxMingxiService;
    @Resource
    private ZxBxMapper zxBxMapper;
    @Resource
    private ZxChengbenMapper zxChengbenMapper;
    @Resource
    private ZxChengbenMingxiMapper zxChengbenMingxiMapper;


    @Override
    @Transactional(rollbackFor = Exception.class)
    public ZxChengbenResult add(ZxBxParam param) {
        ZxBx entity = getEntity(param);
        entity.setCreateUser(LoginContextHolder.getContext().getUserId());
        entity.setCreateUserName(LoginContextHolder.getContext().getUser().getName());
        this.save(entity);
        return getResult(entity);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void delete(ZxBxParam param) {
        ZxBx zxChengben = this.getById(getKey(param));
//        this.removeById(getKey(param));

        //逻辑删除
        zxChengben.setDelFlag(1);
        zxChengben.setUpdateUser(LoginContextHolder.getContext().getUserId());
        zxChengben.setUpdateUserName(LoginContextHolder.getContext().getUser().getName());
        this.updateById(zxChengben);


        if(zxChengben.getCompany()!=null && !zxChengben.getCompany().equals("")){
            //单据号已生成 并提交需要删除生成报销明细
            Map<String,Object> q = new HashMap<>();
            q.put("src_type",zxChengben.getCompany());
            q.put("create_user",LoginContextHolder.getContext().getUserId());
            zxChengbenMingxiMapper.deleteByMap(q);
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void update(ZxChengbenParam param) {
        param.setUpdateUser(LoginContextHolder.getContext().getUserId());
        param.setUpdateUserName(LoginContextHolder.getContext().getUser().getName());
        this.baseMapper.updateByParam(param);

        ZxBxMingxiParam mxpara = new ZxBxMingxiParam();
        mxpara.setBtyZxChengbenId(param.getBtyZxChengbenId());

        List<ZxChengbenMingxiResult> rl = this.zxBxMingxiMapper.customList(mxpara);
        for (ZxChengbenMingxiResult rld : rl) {
            rld.setStatus(param.getStatus());
            ZxBxMingxi entity = new ZxBxMingxi();
            ToolUtil.copyProperties(rld, entity);
            this.zxBxMingxiMapper.updateById(entity);
        }
        if (param.getStatus().equals("2")) {
            //合并货品分类，生成装修报销明细
            List<ZxChengbenMingxiResult> list = zxBxMingxiMapper.mergeDetail(param.getBtyZxChengbenId());
            for (ZxChengbenMingxiResult zxChengbenMingxiResult : list) {
                ZxChengbenMingxi mingxi = new ZxChengbenMingxi();
                ToolUtil.copyProperties(zxChengbenMingxiResult, mingxi);
                mingxi.setCreateUser(LoginContextHolder.getContext().getUserId());
                mingxi.setCreateUserName(LoginContextHolder.getContext().getUser().getName());
                mingxi.setJine(zxChengbenMingxiResult.getBxjine());
                zxChengbenMingxiMapper.insert(mingxi);
            }
        }


        //重新计算门店装修数据
//        if (oldEntity.getBtyZxMdzxId().equals(newEntity.getBtyZxMdzxId())) {
//            //1.修改前后是同一个店
//            mdzxService.calculateChengbenAndYusuan(param.getBtyZxMdzxId());
//        } else {
//            //2.修改前后不是同一个店
//            mdzxService.calculateChengbenAndYusuan(oldEntity.getBtyZxMdzxId());
//            mdzxService.calculateChengbenAndYusuan(newEntity.getBtyZxMdzxId());
//        }
    }

    @Override
    public ZxChengbenResult findBySpec(ZxChengbenParam param) {
        return null;
    }

    @Override
    public List<ZxChengbenResult> findListBySpec(ZxChengbenParam param) {
        return null;
    }

    @Override
    public LayuiPageInfo findPageBySpec(ZxChengbenParam param) {
        Page pageContext = getPageContext();
        IPage page = this.baseMapper.customPageList(pageContext, param);
        return LayuiPageFactory.createPageInfo(page);
    }

    @Override
    public LayuiPageInfo findPageShenhe(ZxChengbenParam param) {
        Page pageContext = getPageContext();
        IPage page = this.baseMapper.customPageShenheList(pageContext, param);
        return LayuiPageFactory.createPageInfo(page);
    }

    @Override
    public LayuiPageInfo findChengbenAndMingXiPageBySpec(ZxChengbenParam param) {
        Page pageContext = getPageContext();
        IPage page = this.baseMapper.customChengbenAndMingXiPageList(pageContext, param);
        return LayuiPageFactory.createPageInfo(page);
    }

    private Serializable getKey(ZxBxParam param) {
        return param.getBtyZxChengbenId();
    }

    private Page getPageContext() {
        return LayuiPageFactory.defaultPage();
    }

    private ZxBx getOldEntity(ZxBxParam param) {
        return this.getById(getKey(param));
    }

    @Override
    public ZxBx getEntity(ZxBxParam param) {
        ZxBx entity = new ZxBx();
        ToolUtil.copyProperties(param, entity);
        return entity;
    }

    public ZxChengbenResult getResult(ZxBx entity) {
        ZxChengbenResult result = new ZxChengbenResult();
        ToolUtil.copyProperties(entity, result);
        return result;
    }


    /**
     * 装修打款的导出
     *
     * @param request
     * @param response
     * @param param
     * @return
     */
    @Override
    public ResponseEntity<byte[]> exportExcel(HttpServletRequest request, HttpServletResponse response, ZxChengbenParam param) {
        try {
            logger.info(">>>>>>>>>>开始导出excel>>>>>>>>>>");
            //门店
            Mdzx mdzx = mdzxMapper.selectById(param.getBtyZxMdzxId());
            //TODO-1 要导出的数据内容
            List<ZxChengbenResult> list = this.baseMapper.customList(param);
            BaseFrontController baseFrontController = new BaseFrontController();
            //文件名称
            String shopName = "装修打款导出文件";


            return baseFrontController.buildResponseEntity(export(list, shopName), shopName + ".xls");
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(">>>>>>>>>>导出excel 异常，原因为：" + e.getMessage());
        }
        return null;
    }

    private InputStream export(List<ZxChengbenResult> list, String shopName) {
        logger.info(">>>>>>>>>>>>>>>>>>>>开始进入导出方法>>>>>>>>>>");
        ByteArrayOutputStream output = null;
        InputStream inputStream1 = null;
        // 保留1000条数据在内存中
        SXSSFWorkbook wb = new SXSSFWorkbook(1000);
        SXSSFSheet sheet = wb.createSheet();
        // 设置报表头样式,cell样式
        CellStyle header = ExcelFormatUtil.headSytle(wb);

        // 报表体样式
        CellStyle content = ExcelFormatUtil.contentStyle(wb);

        //TODO-2 每一列字段名
        String[] titles = new String[]{"日期", "门店", "摘要", "报销人", "金额", "银行卡号", "开户行", "是否打款", "备注"};

        // 字段名所在表格的宽度，数量应与列字段名数量一致
        int[] ints = new int[]{5000, 5000, 5000, 5000, 5000, 5200, 5000, 5000, 5000};

        //TODO-3 设置总标题内容，没有可略过
        //设置第一行标题：指定 4 个参数，起始行，结束行，起始列，结束列,
        int firstRow = 1;
        int lastRow = 1;
        int firstCol = 1;
        int lastCol = 9;
        SXSSFRow row1 = sheet.createRow(0);
        row1.setHeight((short) 600);
        SXSSFCell cell0 = row1.createCell(0);
        CellRangeAddress regionFist = new CellRangeAddress(firstRow - 1, lastRow - 1, firstCol - 1, lastCol - 1);
        sheet.addMergedRegion(regionFist);
        //设置每一列的字段名
        cell0.setCellValue(shopName);
        cell0.setCellStyle(ExcelFormatUtil.titleSytle(wb));

        //TODO-4 设置内容标题
        //内容的标题所在行
        int concentitleStartRow = 2;
        //设置表头样式
        ExcelFormatUtil.initContentTitleEX(sheet, header, concentitleStartRow, titles, ints);
        logger.info(">>>>>>>>>>>>>>>>>>>>表头样式设置完成>>>>>>>>>>");

        //TODO-5 从第contentStartRow行开始为内容数据
        int contentStartRow = 3;
        //总计计算
        //BigDecimal sum = BigDecimal.ZERO;

        if (list != null && list.size() > 0) {
            logger.info(">>>>>>>>>>>>>>>>>>>>开始遍历数据组装单元格内容>>>>>>>>>>");
            for (int i = 0; i < list.size(); i++) {
                ZxChengbenResult result = list.get(i);

                SXSSFRow row = sheet.createRow(contentStartRow + i - 1);
                int j = 0;

                //日期
                SXSSFCell cell = row.createCell(j++);
                if (result.getRiqi() != null) {
                    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                    String date = dateFormat.format(result.getRiqi());
                    cell.setCellValue(date);
                    cell.setCellStyle(content);
                }


                //门店
                cell = row.createCell(j++);
                cell.setCellValue(result.getBrandIdView());
                cell.setCellStyle(content);

                //摘要
                cell = row.createCell(j++);
                cell.setCellValue(result.getZhaiyao());
                cell.setCellStyle(content);

                //报销人
                cell = row.createCell(j++);
                cell.setCellValue(result.getBtyyBxdaIdView());
                cell.setCellStyle(content);

                //金额
                cell = row.createCell(j++);
                cell.setCellValue(result.getZongjia() == null ? "0" : result.getZongjia().toString());
                cell.setCellStyle(content);

                //银行卡号
                cell = row.createCell(j++);
                if (result.getYhKahao() != null && !"".equals(result)) {
                    cell.setCellValue(DataUtil.bankCardNumberFormat(result.getYhKahao()));
                }
                cell.setCellStyle(content);

                //开户行
                cell = row.createCell(j++);
                cell.setCellValue(result.getYhKaihuhang());
                cell.setCellStyle(content);

                //打款状态
                cell = row.createCell(j++);
                String dakuan = "";
                if (result.getDaKuan() != null) {
                    dakuan = "0".equals(result.getDaKuan()) ? "否" : "是";
                }
                cell.setCellValue(dakuan);
                cell.setCellStyle(content);

                //备注
                cell = row.createCell(j++);
                cell.setCellValue(result.getBeizhu());
                cell.setCellStyle(content);

//                //金额总计计算
//                sum = sum.add(result.getZongjia() == null ? BigDecimal.ZERO : result.getZongjia());

            }
            logger.info(">>>>>>>>>>>>>>>>>>>>结束遍历数据组装单元格内容>>>>>>>>>>");
        }

        //TODO-设置总计预估值
//        //所在行
//        int sumRowNum = contentStartRow + list.size();
//        SXSSFRow rowSum = sheet.createRow(sumRowNum - 1);
//        SXSSFCell cellSum = rowSum.createCell(titles.length - 2);
//        cellSum.setCellValue("总计");
//        cellSum.setCellStyle(content);
//
//        SXSSFCell cellSum1 = rowSum.createCell(titles.length - 1);
//        cellSum1.setCellValue(sum.toString());
//        cellSum1.setCellStyle(content);
//
//        //TODO 制表人、时间
//        //所在行
//        int endRow = sumRowNum + 1;
//        SXSSFRow rowEnd = sheet.createRow(endRow - 1);
//        //制表人
//        SXSSFCell cellEnd0 = rowEnd.createCell(0);
//        cellEnd0.setCellValue("制表人：" + LoginContextHolder.getContext().getUser().getName());
//        //制表时间
//        SXSSFCell cellEnd1 = rowEnd.createCell(titles.length - 1);
//        cellEnd1.setCellValue(DateUtil.getNow());

        try {
            output = new ByteArrayOutputStream();
            wb.write(output);
            inputStream1 = new ByteArrayInputStream(output.toByteArray());
            output.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            try {
                if (output != null) {
                    output.close();
                    if (inputStream1 != null) {
                        inputStream1.close();
                    }
                }
            } catch (IOException e) {
                e.printStackTrace();
            }

        }
        return inputStream1;
    }


    private Workbook getWorkBook(InputStream inputStream) throws IOException {
        Workbook wb = null;
        try {
            wb = new XSSFWorkbook(inputStream);
            return wb;

        } catch (Exception e) {
            wb = new HSSFWorkbook(inputStream);
            return wb;
        }

    }

    /**
     * 成本信息导入-成本导入
     *
     * @param fileName
     * @param file
     * @return
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public ResponseData batchImport(String fileName, MultipartFile file) throws Exception {

        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            return new ErrorResponseData("上传文件格式不正确");
        }
        InputStream is = file.getInputStream();
        Workbook wb = getWorkBook(is);

        //获取所有厂家名称
        List<BtyBxdaResult> vendorList = btyBxdaMapper.getAllVendor();
        Map<String, List<BtyBxdaResult>> bxdaMap = vendorList.stream().collect(Collectors.groupingBy(BtyBxdaResult::getXm));

        //获取所有货品信息
        ZxHuopinParam huopinParam = new ZxHuopinParam();
        List<ZxHuopinResult> zxHuopinList = zxHuopinMapper.customList(huopinParam);
        //根据名称分组
        Map<String, List<ZxHuopinResult>> huopinMap = zxHuopinList.stream().collect(Collectors.groupingBy(ZxHuopinResult::getName));

        //excel中多个sheet表，获取所有sheet表的数量
        int sheets = wb.getNumberOfSheets();
        List<ZxChengben> zxChengbenList = new ArrayList<>();
        //for (int i = 0; i < sheets; i++) {
        Sheet sheet = wb.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum() - 1;
        //校验店是否存在
        Row rowFirst = sheet.getRow(0);
        String dianMing = rowFirst.getCell(0).getStringCellValue().trim();

        //工程project表中是否存在该店信息
        ProjectParam projectParam = new ProjectParam();
        projectParam.setName(dianMing);
        ProjectResult projectResult = projectMapper.selectOneByParam(projectParam);
        if (projectResult == null) {
            return new ErrorResponseData("导入失败，门店：" + dianMing + "，在工程项目中不存在，请联系管理员。");
        }

        //如果门店装修表中不存在，不允许导入
        MdzxParam mdzxParam = new MdzxParam();
        mdzxParam.setBtyBrandIdView(dianMing);
        MdzxResult mdzxResult = mdzxMapper.selectOneByParam(mdzxParam);
        //工程项目中已经存在，门店装修中不存在，自动创建门店装修信息
        if (mdzxResult == null) {
            //新建mdzx
            MdzxParam mdzx = new MdzxParam();
            mdzx.setBtyBrandId(projectResult.getId());
            mdzx.setBtyBrandIdView(projectResult.getName());
            mdzx.setCreateUser(LoginContextHolder.getContext().getUserId());
            mdzx.setCreateUserName(LoginContextHolder.getContext().getUser().getName());
            mdzx.setDelFlag(0);
            mdzx.setCreateTime(new Date());
            mdzxService.add(mdzx);
        }

        //成本信息表中已经存在该店信息，是覆盖，还是新增，新增有重复的怎么办？？？


        //循环列
        for (int r = 2; r < lastRowNum; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }

            //厂家名称
            String changJia = row.getCell(0) == null ? "" : row.getCell(0).getStringCellValue();
            BtyBxdaResult btyBxda = bxdaMap.get(changJia.trim()).get(0);
            if (btyBxda == null) {
                return new ErrorResponseData("导入失败，系统中不存在名为：" + changJia + " 的厂家，请联系管理员。");
            }

            //货品名称
            Cell cell1 = row.getCell(1);
            if (ToolUtil.isNotEmpty(cell1)) {
                cell1.setCellType(CellType.STRING);
            }
            String huoPin = row.getCell(1) == null ? "" : row.getCell(1).getStringCellValue();
            //存在多个取第一个
            ZxHuopinResult zxHuopin = huopinMap.get(huoPin).get(0);
            if (zxHuopin == null) {
                return new ErrorResponseData("导入失败，不存在货品：" + huoPin + ",请联系管理员。");
            }

            //单位
            String danWei = (String) getStringValue(row.getCell(2)).getData();
            //尺寸
            String chiCun = (String) getStringValue(row.getCell(3)).getData();

            //数量及校验
            ResponseData typeData = getDoubleValue(row.getCell(4));
            if (typeData.getCode() != 200) {
                return new ErrorResponseData("导入失败，数量必须为数字，第" + (r + 1) + "行" + 5 + "列,数量不是数字");
            }
            double d = (double) typeData.getData();
            BigDecimal shuliang = row.getCell(4) == null ? BigDecimal.ZERO : new BigDecimal(d);

            //单价及校验
            typeData = getDoubleValue(row.getCell(5));
            if (typeData.getCode() != 200) {
                return new ErrorResponseData("导入失败，数量必须为数字，第" + (r + 1) + "行" + 6 + "列,数量不是数字");
            }
            d = (double) typeData.getData();
            BigDecimal jiaGe = row.getCell(5) == null ? BigDecimal.ZERO : new BigDecimal(d);


            //插入数据
            ZxChengben chengBen = new ZxChengben();
            //厂家
            chengBen.setBtyyBxdaId(btyBxda.getYhk());
            chengBen.setBtyyBxdaIdView(btyBxda.getXm());
            //货品
            chengBen.setBtyZxHuopinId(zxHuopin.getBtyZxHuopinId());
            chengBen.setBtyZxHuopinIdView(zxHuopin.getName());
            //单位
            chengBen.setDanwei(danWei);
            //尺寸
            chengBen.setChicun(chiCun);
            //数量
            chengBen.setShuliang(shuliang);
            //店价
            chengBen.setJiage(jiaGe);
            //总价
            chengBen.setZongjia(chengBen.getJiage().multiply(chengBen.getShuliang()));

            zxChengbenList.add(chengBen);

        }
        // }

        if (CollectionUtil.isNotEmpty(zxChengbenList)) {
            for (ZxChengben hycy : zxChengbenList) {
                //this.baseMapper.insert(hycy);

            }
        }

        System.out.println(zxChengbenList.toString());

        return ResponseData.success();

    }

    /**
     * 成本信息导入-成本导入-最新
     *
     * @param fileName
     * @param file
     * @return
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public ResponseData batchImportBaoXiaoDan(String fileName, MultipartFile file) throws Exception {

        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            return new ErrorResponseData("上传文件格式不正确");
        }
        InputStream is = file.getInputStream();
        Workbook wb = getWorkBook(is);

        //获取所有的门店装修的门店信息
        MdzxParam mdzxParam = new MdzxParam();
        mdzxParam.setContainsCopy("no");
        List<MdzxResult> mdzxResultList = mdzxService.customList(mdzxParam);

        //获取所有厂家名称-报销人
        List<BtyBxdaResult> vendorList = btyBxdaMapper.getAllVendor();
        Map<String, List<BtyBxdaResult>> bxdaMap = vendorList.stream().collect(Collectors.groupingBy(BtyBxdaResult::getXm));

        //获取所有的二级科目-全
        List<ZxTzqkViewResult> zxKemuResultList2 = zxKemuMapper.selectAllErjiKemu("2");
        Map<String, Long> erjiKeMuMap = zxKemuResultList2.stream().collect(Collectors.toMap(ZxTzqkViewResult::getName, ZxTzqkViewResult::getSecondLevelId));

        //获取所有的二级科目对应关系
        ZxKemuKeyParam zxKemuKeyParam = new ZxKemuKeyParam();
        List<ZxKemuKeyResult> zxKemuKeyResultList = zxKemuKeyMapper.customList(zxKemuKeyParam);
        Map<String, String> zxKemuKeyResultMap = zxKemuKeyResultList.stream().collect(Collectors.toMap(ZxKemuKeyResult::getKeyword, ZxKemuKeyResult::getSecondLevelName));


        //excel中多个sheet表，获取所有sheet表的数量
        int sheets = wb.getNumberOfSheets();
        List<ZxBx> zxChengbenList = new ArrayList<>();
        Map<String, ZxBxMingxiParam> mxMap = new HashMap();

        Sheet sheet = wb.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();

        //循环列
        for (int r = 1; r <= lastRowNum; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            if (row.getCell(0) == null && row.getCell(1) == null && row.getCell(2) == null) {//前3列若为null则视为结束
                break;
            }
            ResponseData riDate = getDateVal(row.getCell(0));
            if (riDate.getCode() != 200) {
                return new ErrorResponseData("导入失败，日期必须为日期类型，第" + (r + 1) + "行" + 1 + "列,不是日期类型");
            }
            DateFormat formatDate = new SimpleDateFormat("yyyy-MM-dd");
            Date riqi = null;
            String riqiStr = (String) riDate.getData();
            if (riqiStr != null && !"".equals(riqiStr)) {
                riqi = formatDate.parse(riqiStr);
            }

            //序号
            double xuhaoD = row.getCell(1).getNumericCellValue();
            String xuhao = (int) xuhaoD + "";


            //校验店是否存在
            String dianMing = row.getCell(2).getStringCellValue().trim();
            //门店可以为空

//            if (ToolUtil.isEmpty(dianMing)) {
//                return new ErrorResponseData("门店信息不允许为空");
//            }

            //工程项目中已经存在，门店装修中不存在，自动创建门店装修信息
            MdzxResult md = queryMdMohu(dianMing, mdzxResultList);

            //摘要
            String zhaiyao = row.getCell(3) == null ? "" : row.getCell(3).getStringCellValue();

            //金额
            Cell cell3 = row.getCell(4);
            String fluma = cell3.toString();
            //金额及校验
            ResponseData typeData = getDoubleValue(cell3);
            if (typeData.getCode() != 200) {
                return new ErrorResponseData("导入失败，金额必须为数字，第" + (r + 1) + "行" + 5 + "列,金额不是数字");
            }

            System.out.println("d=" + typeData.getData());

            double d = (double) typeData.getData();
            BigDecimal jine = cell3 == null ? BigDecimal.ZERO : new BigDecimal(d).setScale(4, BigDecimal.ROUND_HALF_UP);

            //报销人
            Cell cell4 = row.getCell(5);
            String baoxiaoRen = cell4 == null ? "" : cell4.getStringCellValue();

            //卡号
            Cell cell5 = row.getCell(6);
            String kaHao = cell5 == null ? "" : cell5.getStringCellValue().replace(" ", "");

            //开户行
            Cell cell6 = row.getCell(7);
            String kaihuHang = cell6 == null ? "" : cell6.getStringCellValue();

            //备注
            Cell cell7 = row.getCell(8);
            String beizhu = cell7 == null ? "" : cell7.getStringCellValue();

            //打款类型
            Cell cell8 = row.getCell(9);
            String dakuan = cell8 == null ? "" : cell8.getStringCellValue();

            //二级科目
            Cell cell9 = row.getCell(10);
            String erjikm = cell9 == null ? "" : cell9.getStringCellValue();

            //根据装修科目关系设置打款类型 1.填写了二级科目，使用填写的 2.未填写使用关键字查询
            if (erjikm.equals("")) {
                //为空时表示未填写，使用关键字查询对应科目
                if (CollectionUtil.isNotEmpty(zxKemuKeyResultMap)) {
                    if (!zhaiyao.equals("")) {
                        String erjikmName = zxKemuKeyResultMap.get(zhaiyao.trim());
                        if (!erjikmName.equals("")) {
                            erjikm = erjikmName;
                        }
                    }
                }
            }
            //批号
            Cell cell10 = row.getCell(11);
            String picihao = cell10 == null ? "" : cell10.getStringCellValue();

            //插入数据
            ZxBx chengBen = new ZxBx();
            chengBen.setRiqi(riqi);
            //门店
            if (md != null) {
                chengBen.setBtyZxMdzxId(md.getBtyZxMdzxId());
            }
            //摘要
            chengBen.setZhaiyao(zhaiyao);
            //金额
            System.out.println("jine=" + jine.toString());
            chengBen.setZongjia(jine);
            //报销人
            chengBen.setBtyyBxdaIdView(baoxiaoRen);
            //卡号
            chengBen.setYhKahao(kaHao);
            //报销人id
            if (bxdaMap.get(baoxiaoRen) != null) {
                List<BtyBxdaResult> bxdaResultList = bxdaMap.get(baoxiaoRen);
                Map<String, String> baoxiaoRenMap = bxdaResultList.stream().collect(Collectors.toMap(BtyBxdaResult::getYhk, BtyBxdaResult::getXm));
                if (baoxiaoRenMap.get(kaHao) != null && baoxiaoRenMap.get(kaHao).equals(baoxiaoRen)) {
                    chengBen.setBtyyBxdaId(baoxiaoRenMap.get(baoxiaoRen));
                }
            }
            //开户行
            chengBen.setYhKaihuhang(kaihuHang);
            //备注
            chengBen.setBeizhu(beizhu);
            //打款类型
            chengBen.setDaKuan(dakuan);
//            //默认打款
//            chengBen.setDaKuan("1");
            chengBen.setLeixing("1");

            chengBen.setDanwei(xuhao);
            chengBen.setBtyZxHuopinIdView(picihao);
            if (md == null) {
                chengBen.setBeizhu(chengBen.getBeizhu() + dianMing);
            }
            zxChengbenList.add(chengBen);
            if (md != null && ToolUtil.isNotEmpty(erjikm) && erjiKeMuMap.get(erjikm) != null) {//门店存在&& 二级科目不为空&二级科目有该科目时，则插入明细数据
                ZxBxMingxiParam mx = new ZxBxMingxiParam();
                mx.setBtyZxMdzxId(md.getBtyZxMdzxId());
                mx.setBtyZxKemuId(erjiKeMuMap.get(erjikm));
                mx.setJine(jine);
                mx.setQtkm(zhaiyao);
//                mx.setGongshi(fluma);
                mxMap.put(xuhao, mx);
            }
        }

        if (CollectionUtil.isNotEmpty(zxChengbenList)) {
            for (ZxBx chengben : zxChengbenList) {
                this.baseMapper.insert(chengben);
                ZxBxMingxiParam mingxi = mxMap.get(chengben.getDanwei());
                if (mingxi != null) {
                    mingxi.setBtyZxChengbenId(chengben.getBtyZxChengbenId());
                    //this.zxChengbenMingxiService.add(mingxi);
                    this.zxBxMingxiService.add(mingxi);
                }
            }
        }
        return ResponseData.success();

    }

    /**
     * 投资信息导入
     *
     * @param fileName
     * @param file
     * @return
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public ResponseData importTz(String fileName, MultipartFile file) throws Exception {
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            return new ErrorResponseData("上传文件格式不正确");
        }
        InputStream is = file.getInputStream();
        Workbook wb = getWorkBook(is);
        return HandWorkbook(wb);
    }

    //解析excel
    private ResponseData HandWorkbook(Workbook wb) throws Exception {
        //获取所有的门店装修的门店信息
        MdzxParam mdzxParam = new MdzxParam();
        mdzxParam.setContainsCopy("no");
        List<MdzxResult> mdzxResultList = mdzxService.customList(mdzxParam);
        Map<String, Long> mdzxMap = mdzxResultList.stream().collect(Collectors.toMap(MdzxResult::getBtyBrandIdView, MdzxResult::getBtyZxMdzxId));


        //获取所有的二级科目-全
        List<ZxTzqkViewResult> zxKemuResultList2 = zxKemuMapper.selectAllErjiKemu("2");
        Map<String, Long> erjiKeMuMap = zxKemuResultList2.stream().collect(Collectors.toMap(ZxTzqkViewResult::getName, ZxTzqkViewResult::getSecondLevelId));

        //获取所有厂家名称-报销人
//        List<BtyBxdaResult> vendorList = btyBxdaMapper.getAllVendor();
//        Map<String, List<BtyBxdaResult>> bxdaMap = vendorList.stream().collect(Collectors.groupingBy(BtyBxdaResult::getXm));

        //excel中多个sheet表，获取所有sheet表的数量
        int sheets = wb.getNumberOfSheets();
        List<ZxBx> zxChengbenList = new ArrayList<>();
        List<ZxBxMingxi> mxList = new ArrayList<>();

        Sheet sheet = wb.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();

        Long btyZxMdzxId = null;
        String km1 = "";
        String km2 = "";
        ZxBx chengBen = new ZxBx();
        double total = 0;

        for (int r = 2; r <= lastRowNum; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }

            if (r == 2) {//获取店名
                Cell cell = row.getCell(1);
                String dm = cell.getStringCellValue().trim();
                //工程项目中已经存在，门店装修中不存在，自动创建门店装修信息
                if (mdzxMap.get(dm) == null) {
                    return new ErrorResponseData("未找到门店信息，请先添加装修门店信息");
                }
                btyZxMdzxId = mdzxMap.get(dm);
                //查询是否已导入
                ZxChengbenParam param = new ZxChengbenParam();
                param.setBtyZxMdzxId(btyZxMdzxId);
                param.setRiqi("2020-11-30");
                int exist = this.baseMapper.isExist(param);
                if (exist > 0) {
                    return new ErrorResponseData("已经导入，不允许重复导入");
                }
            } else if (r == 3) {//获取总面积
//                Cell cell = row.getCell(1);
            } else if (r == 4) {//获取预估
//                Cell cell = row.getCell(1);
            } else if (r == lastRowNum) {//获取制表人，制表日期
            } else {
                ZxBxMingxi mx = new ZxBxMingxi();
                Cell km1cell = row.getCell(0);
                Cell km2cell = row.getCell(1);
                String km1val = km1cell.getStringCellValue().trim().replaceAll("　", "");
                km1 = "".equals(km1val) ? km1 : km1val;
                km2 = km2cell.getStringCellValue().trim();
                if ("总计".equals(km1)) {
                    break;//总计后的不做处理
                }
                if (ToolUtil.isEmpty(km2) || "二级科目".equals(km2)) {
                    continue;
                }

                //金额及校验
                Cell jecell = row.getCell(2);
//                String jeStr = jecell.getStringCellValue().trim();
                String gongshi = jecell.toString();
                ResponseData typeData = getDoubleValue(jecell);
                if (ToolUtil.isNotEmpty(typeData.getData())) {
                    if (Double.valueOf(typeData.getData().toString()) == 0.0d) {
                        continue;
                    }
                }
                if (typeData.getCode() != 200) {
                    return new ErrorResponseData("导入失败，‘" + km2 + "'金额不是数字");
                }
                double money = (double) typeData.getData();
//                BigDecimal money = new BigDecimal(data);
                total += money;
                mx.setJine(new BigDecimal(money));

                Cell bzcell = row.getCell(3);
                String bz = bzcell.getStringCellValue().trim();
                mx.setQtkm(bz);

                mx.setBeizhu("投资情况导入");

                Long kemu2 = erjiKeMuMap.get(km2);
                if (kemu2 == null) {
                    return new ErrorResponseData("’" + kemu2 + "‘ 二级科目不存在");
                }
                mx.setBtyZxKemuId(kemu2);
                mx.setBtyZxMdzxId(btyZxMdzxId);
                mx.setGongshi(gongshi);
                mxList.add(mx);
            }

        }

        chengBen.setRiqi(DateUtil.getDateByStr("2020-11-30"));
        chengBen.setZhaiyao("投资情况初始化");
        chengBen.setDaKuan("1");
        chengBen.setLeixing("1");
//        chengBen.setCreateTime(new Date());
        chengBen.setBtyZxMdzxId(btyZxMdzxId);
        chengBen.setZongjia(new BigDecimal(total));

        int insert = this.baseMapper.insert(chengBen);
        if (insert > 0) {
            for (ZxBxMingxi mx : mxList) {
                mx.setBtyZxChengbenId(chengBen.getBtyZxChengbenId());
                this.zxBxMingxiMapper.insert(mx);
            }
        }
        return ResponseData.success();
    }

    /**
     * 批量导入
     *
     * @return
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public ResponseData btnImportTzBatch() throws Exception {
        String excelPath = "D:/data/temp/";
        File dir = new File(excelPath);
        File[] files = dir.listFiles();
        List<String> logs = new ArrayList<>();
        for (File f : files) {
            String fname = f.getName();
            if (!fname.matches("^.+\\.(?i)(xls)$") && !fname.matches("^.+\\.(?i)(xlsx)$")) {
                //非excel文件不做处理
                continue;
            }
            FileInputStream fis = new FileInputStream(f);
            Workbook workBook = getWorkBook(fis);
            ResponseData rs = HandWorkbook(workBook);
            if (rs.getCode() != 200) {
                logs.add("'" + fname + "' 导入失败，原因：" + rs.getMessage());
            } else {
                logs.add("'" + fname + "' 导入成功");
            }
        }
        FileUtils.write2File(excelPath, "投资情况导入" + DateUtil.getStampNow() + ".txt", logs);
        return SuccessResponseData.success();
    }

    private MdzxResult queryMdMohu(String name, List<MdzxResult> mdzxResultList) {
        if (name == null || "".equals(name)) {
            return null;
        }
        if (mdzxResultList == null || mdzxResultList.isEmpty()) {
            return null;
        }
        List<MdzxResult> mdzx_list = new ArrayList<MdzxResult>();
        for (MdzxResult r : mdzxResultList) {
            String mc = r.getBtyBrandIdView();
            if (mc.indexOf(name) > -1) {
                mdzx_list.add(r);
            }
        }
        if (mdzx_list.isEmpty() || mdzx_list.size() > 1) {
            return null;
        }
        return mdzx_list.get(0);
    }

    private List<MdzxResult> mdzxResultList = null;

    @Override
    public MdzxResult queryMdMohu(String name) {
        if (mdzxResultList == null || mdzxResultList.isEmpty()) {
            MdzxParam mdzxParam = new MdzxParam();
            mdzxParam.setContainsCopy("no");
            mdzxResultList = mdzxService.customList(mdzxParam);
        }
        return queryMdMohu(name, mdzxResultList);
    }

    @Override
    public ResponseData kemuCorrection() {
        //TODO-1获取所有的成本记录
        ZxChengbenParam zxChengbenParam = new ZxChengbenParam();
        List<ZxChengbenResult> allChengBenList = this.baseMapper.customList(zxChengbenParam);

        //TODO-2如果明细中的科目和其他科目（含关键字<---科目对应关系）不一致的，更改对应关系。找不到的不更改
        //TODO-2.1所有的科目对应关系
        ZxKemuKeyParam zxKemuKeyParam = new ZxKemuKeyParam();
        List<ZxKemuKeyResult> zxKemuKeyResultList = zxKemuKeyMapper.customList(zxKemuKeyParam);
        Map<String, String> allKemuKeyMap = zxKemuKeyResultList.stream().collect(Collectors.toMap(ZxKemuKeyResult::getKeyword, ZxKemuKeyResult::getSecondLevelName));
        //TODO-2.2遍历所有的成本记录对应的明细记录
        //TODO-2.2.1所有的明细记录
        ZxBxMingxiParam mingxiParam = new ZxBxMingxiParam();
        List<ZxChengbenMingxiResult> allMingxiList = zxBxMingxiMapper.customList(mingxiParam);
        Map<Long, List<ZxChengbenMingxiResult>> allMingxiMap = allMingxiList.stream().collect(Collectors.groupingBy(ZxChengbenMingxiResult::getBtyZxChengbenId));


        return null;
    }

    @Override
    public void updateForMingxi(Long btyZxChengbenId) {
        //获取子表信息
        ZxBxMingxiParam para = new ZxBxMingxiParam();
        para.setBtyZxChengbenId(btyZxChengbenId);

        List<ZxChengbenMingxiResult> mingxiResultList = zxBxMingxiMapper.customList(para);

        ZxBx md = this.baseMapper.selectById(btyZxChengbenId);


        //同步金额
        BigDecimal je = new BigDecimal("0.00");
        for (ZxChengbenMingxiResult mx : mingxiResultList) {
            if(mx.getJine()==null||mx.getJine().equals("")){
                continue;
            }
            je = je.add(mx.getJine());
        }
        md.setZongjia(je);

        //同步审核金额
        BigDecimal shje = new BigDecimal("0.00");
        for (ZxChengbenMingxiResult mx : mingxiResultList) {
            if (ToolUtil.isNotEmpty(mx.getShje())) {
                shje = shje.add(mx.getShje());
            }
        }
        md.setShje(shje);
        this.baseMapper.updateById(md);
    }

    public static void main(String[] args) throws Exception {
        String abc = "2222345q2";
        System.out.println(abc.indexOf("2"));
        String fileName = "2222345.q2.xlsx";

        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            System.out.println("1");
        }
    }

    @Override
    public ResponseData dakuan_queren(ZxChengbenParam zxChengbenParam) {
        Long id = zxChengbenParam.getBtyZxChengbenId();
        ZxBx cb = this.getById(id);
        cb.setStatus("1");
        cb.setStatusView("已打款");
        this.saveOrUpdate(cb);
        return ResponseData.success();
    }

    @Override
    public ResponseData dakuan_quxiao(ZxChengbenParam zxChengbenParam) {
        Long id = zxChengbenParam.getBtyZxChengbenId();
        ZxBx cb = this.getById(id);
        cb.setStatus("0");
        cb.setStatusView("未打款");
        this.saveOrUpdate(cb);
        return ResponseData.success();
    }

    private static ResponseData getDateVal(Cell cell) {
        CellType cellType = cell.getCellTypeEnum();
        switch (cellType) {
            case NUMERIC:
                String value;
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    SimpleDateFormat formatt = new SimpleDateFormat("yyyy-MM-dd");
                    value = formatt.format(date);
                } else {
                    double dValue = cell.getNumericCellValue();
                    DecimalFormat df = new DecimalFormat("0");
                    value = df.format(dValue);
                }
                return new SuccessResponseData(value);
            case BLANK:
                return new SuccessResponseData(null);
            case ERROR:
                return new ErrorResponseData(String.valueOf(cell.getErrorCellValue()));
            default:
                return new ErrorResponseData("不是日期类型");
        }
    }


    /**
     * 利用正则表达式判断字符串是否是数字
     *
     * @param str
     * @return
     */
    private boolean isNumeric(String str) {
        Pattern pattern = Pattern.compile("^[0-9]*$");
        Matcher isNum = pattern.matcher(str);
        if (!isNum.matches()) {
            return false;
        }
        return true;
    }

    /**
     * 校验double类型
     *
     * @param str
     * @return
     */
    private boolean isDouble(String str) {
        try {
            Double.parseDouble(str);
            return true;
        } catch (NumberFormatException ex) {
        }
        return false;
    }


    private ResponseData getDoubleValue(Cell cell) {
        if (cell == null) {
            return new SuccessResponseData(new Double(0));
        }

        CellType cellType = cell.getCellType();
        if (cellType == NUMERIC) {
            //数值型
            double numericCellValue = cell.getNumericCellValue();
            return new SuccessResponseData(numericCellValue);
        } else if (cellType == CellType.BLANK) {
            //空值型
            return new SuccessResponseData(new Double(0));
        } else if (cellType == CellType.FORMULA) {
            //公式型
            cell.setCellType(CellType.STRING);
            String str = cell.getStringCellValue();
            return new SuccessResponseData(Double.parseDouble(str));
        } else {
            //字符串型
            String str = cell.getStringCellValue();
            if (str.trim().equals("")) {
                str = "0";
            }
            System.out.println("double str=" + str);
            try {
                double value = Double.parseDouble(str);
                return new SuccessResponseData(value);
            } catch (NumberFormatException ex) {
            }
            return new ErrorResponseData("不是数字");
        }
    }

    private ResponseData getIntegerValue(Cell cell) {
        if (cell == null) {
            return new SuccessResponseData(new Integer(0));
        }
        CellType cellType = cell.getCellType();
        if (cellType == NUMERIC) {
            //数值型
            Double numericCellValue = cell.getNumericCellValue();

            int valInt = Double.valueOf(numericCellValue).intValue();
            return new SuccessResponseData(valInt);
        } else if (cellType == CellType.BLANK) {
            //空值型
            return new SuccessResponseData(new Integer(0));
        } else if (cellType == CellType.FORMULA) {
            //公式型
            cell.setCellType(CellType.STRING);
            String str = cell.getStringCellValue();
            return new SuccessResponseData(Integer.parseInt(str));
        } else {
            //字符串型
            String str = cell.getStringCellValue();
            if (str.trim().equals("")) {
                str = "0";
            }
            try {
                double value = Double.parseDouble(str);
                int valInt = Double.valueOf(value).intValue();
                return new SuccessResponseData(valInt);
            } catch (NumberFormatException ex) {
            }
            return new ErrorResponseData("不是数字");
        }
    }

    private ResponseData getStringValue(Cell cell) {
        if (cell == null) {
            return new SuccessResponseData("");
        }

        CellType cellType = cell.getCellType();
        if (cellType == NUMERIC) {
            Double numericCellValue = cell.getNumericCellValue();
            return new SuccessResponseData(numericCellValue.toString());
        } else if (cellType == CellType.BLANK) {
            //空值型
            return new SuccessResponseData("");
        } else {
            String str = cell.getStringCellValue();
            try {
                return new SuccessResponseData(str);
            } catch (NumberFormatException ex) {
            }
            return new ErrorResponseData("解析失败");
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public ResponseData audit(ZxBxParam param) {
        ZxBx entity = getEntity(param);
        this.updateById(entity);

        ZxBxMingxiParam p = new ZxBxMingxiParam();
        p.setBtyZxChengbenId(param.getBtyZxChengbenId());
        p.setStatus(param.getStatus());
        zxBxMingxiService.updateBySpec(p);

        return ResponseData.success();
    }

    @Override
    public void chehui(Long btyZxChengbenId) throws Exception{

        ZxBx vo = this.baseMapper.selectById(btyZxChengbenId);
        if(vo.getStatus().equals("3")||vo.getStatus().equals("5")){
            if(vo.getCompany()!=null && !vo.getCompany().equals("")){
                Map<String,Object> delMap =new HashMap<String,Object>();
                delMap.put("src_type",vo.getCompany());
                delMap.put("create_user",LoginContextHolder.getContext().getUserId());

                List<ZxChengbenMingxi> list = zxChengbenMingxiMapper.selectByMap(delMap);
                if(list!=null && !list.isEmpty()){
                    for(ZxChengbenMingxi mx:list){
                        if(mx.getStatus().equals("5")||mx.getStatus().equals("3")){
                            vo.setStatus("4");
                            this.baseMapper.updateById(vo);
                            zxChengbenMingxiMapper.deleteByMap(delMap);
                        }else {
                            throw new Exception("已审批，不能修改");
                        }
                    }
                }
            }
        }
    }
}
